Home:ALL Converter>Does MongoDB stages should avoid FETCH when INDEX is matching?

Does MongoDB stages should avoid FETCH when INDEX is matching?

Ask Time:2019-10-25T03:40:59         Author:Rafael

Json Formatter

According to the docs, MongoDB should skip the FETCH stage when an index covers a query.

If I correctly understood, this sentences explain this behavior:

Covered Queries When an index covers a query, MongoDB can both match the query conditions and return the results using only the index keys; i.e. MongoDB does not need to examine documents from the collection to return the results.

When an index covers a query, the explain result has an IXSCAN stage that is not a descendant of a FETCH stage, and in the executionStats, the totalDocsExamined is 0.

In earlier versions of MongoDB, cursor.explain() returned the indexOnly field to indicate whether the index covered a query. (https://docs.mongodb.com/manual/reference/explain-results/)

And this

With this in place, the query takes less than 2 Ms. Because the index ‘covered’ the query, MongoDB was able to match the query conditions and return the results using only the index keys; without even needing to examine documents from the collection to return the results. (if you see an IXSCAN stage that is not a child of a FETCH stage, in the execution plan then the index ‘covered’ the query.) (https://studio3t.com/knowledge-base/articles/mongodb-index-strategy/)

But in a test scenario it doesn't occur:

Example to test:

    db.Test.insert({"Field1":"data on field1: 1","Field2":"data on field2: 1"});
    db.Test.insert({"Field1":"data on field1: 2","Field2":"data on field2: 2"});
    db.Test.insert({"Field1":"data on field1: 3","Field2":"data on field2: 3"});
    db.Test.insert({"Field1":"data on field1: 4","Field2":"data on field2: 4"});
    db.Test.insert({"Field1":"data on field1: 5","Field2":"data on field2: 5"});
    db.Test.insert({"Field1":"data on field1: 6","Field2":"data on field2: 6"});

After I created an index to Field2.

db.Test.createIndex({"Field2":1})

And then I query the collection:

db.Test.find({"Field2":"data on field2: 5"}).explain("executionStats");

I expected a stage IDXSCAN that isn't child of a FETCH stage. But the output is like this:

[...]
"winningPlan" : {
            "stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",
                "keyPattern" : {
                    "Campo2" : 1.0
                },
                "indexName" : "Field2_1",
                "isMultiKey" : false,
                "multiKeyPaths" : {
                    "Campo2" : []
                },
                "isUnique" : false,
                "isSparse" : false,
                "isPartial" : false,
                "indexVersion" : 2,
                "direction" : "forward",
                "indexBounds" : {
                    "Field2" : [ 
                        "[\"data on field2: 5", \"data on field2: 5\"]"
                    ]
                }
            }
        },
[...]

There are two stages: one "stage" : "FETCH", and its child "stage" : "IXSCAN",.

Can anyone explain what I am misunderstanding?

*** ABOUT PROJECTION

When running query with projection

"winningPlan" : {
            "stage" : "PROJECTION",
            "transformBy" : {
                "Campo2" : 1.0
            },
            "inputStage" : {
                "stage" : "FETCH",
                "inputStage" : {
                    "stage" : "IXSCAN",
                    "keyPattern" : {
                        "Field2" : 1.0
                    },
                    "indexName" : "Field2_1",
                    "isMultiKey" : false,
                    "multiKeyPaths" : {
                        "Campo2" : []
                    },
                    "isUnique" : false,
                    "isSparse" : false,
                    "isPartial" : false,
                    "indexVersion" : 2,
                    "direction" : "forward",
                    "indexBounds" : {
                        "Field2" : [ 
                            "[\"data on field2: 5", \"data on field2: 5\"]"
                        ]
                    }
                }
            }
        },

With Adam's answer: it worked!

I realized that projection shouldn't include "_id" to avoid FETCH.

Author:Rafael,eproduced under the CC 4.0 BY-SA copyright license with a link to the original source and this disclaimer.
Link to original article:https://stackoverflow.com/questions/58548141/does-mongodb-stages-should-avoid-fetch-when-index-is-matching
yy